package com.glsc.ngateway.common.base.repo.mysql.gateway.other;

import com.glsc.ngateway.common.base.domain.mysql.gateway.other.SystemConfig;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.validation.constraints.NotNull;
import java.util.List;

/**
 * @Author: zhaoxc
 * @Date: 2020/6/23 17:50
 * @Desc:
 */
@Repository
public interface SystemConfigRepository extends JpaRepository<SystemConfig, Integer>, JpaSpecificationExecutor<SystemConfig> {

    /**
     * 根据配置项唯一编码查询
     */
    SystemConfig findByConfigCode(String configCode);

    /**
     * 根据配置项唯一编码列表查询
     */
    List<SystemConfig> findByConfigCodeIn(List<String> configCodeList);

    /**
     * 获取数据库当前时间
     */
    @Query(value = "select sysdate()", nativeQuery = true)
    java.sql.Timestamp getSysdate();

    /**
     * 获取数据库若干月、日前后的日期
     * @param beforeMonths 整数表示向前多少个月，负数表示向后多少个月
     * @param beforeDays 整数表示向前多少天，负数表示向后多少天
     */
    @Query(value = "select DATE_FORMAT(date_sub(date_sub(sysdate(),interval :beforeMonths month),interval :beforeDays day),'%Y-%m-%d') as newdate", nativeQuery = true)
    String getBeforeMonthDay(@Param("beforeMonths") Integer beforeMonths,@Param("beforeDays") Integer beforeDays);

    /**
     * 获取数据库若干月、日前后的日期
     * 如果前多少月、日，beforeMonths和beforeDays为负数向后推算；正数，则标识向前推算
     * @param benchDate 基准日期 格式yyyymmdd
     * @param beforeMonths 整数表示向前多少个月，负数表示向后多少个月
     * @param beforeDays 整数表示向前多少天，负数表示向后多少天
     * @param exchange 交易所，ND表示自然日、SSE表示按照上交所工作日
     *
     * @return 返回yyyy-mm-dd格式的日期字符串
     */
    @Query(value = "select DATE_FORMAT(date_sub(date_sub( (select trade_days from t_glam_trade_calendar where exchange =:exchange and vc_trade_dt =replace(:benchDate,'-','') ) ,interval :beforeMonths month),interval :beforeDays day),'%Y-%m-%d') as newdate", nativeQuery = true)
    String getBeforeMonthDay(@Param("benchDate") String benchDate,@Param("beforeMonths") Integer beforeMonths,@Param("beforeDays") Integer beforeDays,@Param("exchange") String exchange);


    /**
     * 获取某个自然日、交易日后若干日的日期
     * afterDays为正数，表示为当前日期向后若干天数
     * @param curDate 日期格式为yyyymmdd
     */
    @Query(value = "select DATE_FORMAT(trade_days,'%Y-%m-%d') as datestr from t_glam_trade_calendar where exchange =:exchange and vc_trade_dt>=replace(:curDate,'-','') order by  vc_trade_dt limit :afterDays,1", nativeQuery = true)
    String getDateAfterDays(@Param("curDate") String curDate,@Param("afterDays") Integer afterDays,@Param("exchange") String exchange);

    /**
     * 以某个日期为基准，向前或向后若干年、月、天数，以自然天为基准
     *
     * @param yearAdd  正数，表示为当前日期向后若干天数；负数向前若干年
     * @param monthAdd
     * @param dayAdd   afterDays为正数，表示为当前日期向后若干天数
     * @param curDate  日期格式为yyyymmdd
     */
    @Query(value = "select DATE_FORMAT( date_add(date_add( date_add( (select trade_days from t_glam_trade_calendar where vc_trade_dt=replace(:curDate,'-','') and exchange ='ND'),interval :yearAdd YEAR), interval :monthAdd MONTH ), interval :dayAdd DAY) ,  '%Y-%m-%d')", nativeQuery = true)
    String getDateAdd(@Param("curDate") String curDate, @Param("yearAdd") Integer yearAdd, @Param("monthAdd") Integer monthAdd, @Param("dayAdd") Integer dayAdd);

    /**
     * 获取特定日期向后交易日天数
     * afterDays为正数，表示为当前日期向后若干天数
     * @param tradeDate 日期格式为yyyymmdd
     */
    @Query(value = "select DATE_FORMAT(trade_days,'%Y-%m-%d') as datestr from t_glam_trade_calendar where exchange ='SSE' and vc_trade_dt>=:tradeDate order by  vc_trade_dt limit :afterDays,1", nativeQuery = true)
    String getTradeDateAfterDays(@Param("tradeDate") String tradeDate,@Param("afterDays") Integer afterDays);

    /**
     * 返回指定月份最后一个日期，如果exchange=TRADE_EXCHANGE_ND，表示自然日的月末一日；如果exchange=TRADE_EXCHANGE_SSE，表示取上证当月最后一个交易日
     */
    @Query(value = "select DATE_FORMAT(max(trade_days),'%Y-%m-%d') as datestr from t_glam_trade_calendar a where left(vc_trade_dt,6)=left(replace(:dateStr,'-',''),6) and exchange =:exchange", nativeQuery = true)
    String getMonthLastDate(@Param("dateStr") String dateStr,@Param("exchange") String exchange);

    /**
     * 返回指定月份第一个交易日
     */
    @Query(value = "select DATE_FORMAT(min(trade_days),'%Y-%m-%d') as datestr from t_glam_trade_calendar a where left(vc_trade_dt,6)=left(replace(:dateStr,'-',''),6) and exchange =:exchange", nativeQuery = true)
    String getMonthFirstTradeDate(@Param("dateStr") String dateStr,@Param("exchange") String exchange);

    /**
     * 查询指定交易所的日期
     */
    @Query(value = "select DATE_FORMAT(min(trade_days),'%Y-%m-%d') as datestr from t_glam_trade_calendar a where left(vc_trade_dt,6)=left(replace(:dateStr,'-',''),6) and exchange =:exchange", nativeQuery = true)
    String getExchangeDay(@Param("dateStr") String dateStr,@Param("exchange") String exchange);

    /**
     * 获取指定日期后面第几周的周一
     */
    @Query(value = "select subdate(date_add(trade_days,INTERVAL :weekOffset WEEK),if(date_format(date_add(trade_days,INTERVAL :weekOffset WEEK),'%w')=0,7,date_format(date_add(trade_days,INTERVAL :weekOffset WEEK),'%w'))-1) from t_glam_trade_calendar where vc_trade_dt=:dateStr and exchange =:exchange", nativeQuery = true)
    String getMondayDateByWeekOffset(@Param("dateStr") String dateStr, @Param("weekOffset") Integer weekOffset, @Param("exchange") String exchange);

    /**
     * 获取指定日期前后几周对应日期
     */
    @Query(value = "select DATE_FORMAT( date_add( (select trade_days from t_glam_trade_calendar where vc_trade_dt=replace(:dateStr,'-','') and exchange ='ND'),interval :weekOffset WEEK),  '%Y-%m-%d')", nativeQuery = true)
    String getDateByWeekOffset(@Param("dateStr") String dateStr, @Param("weekOffset") Integer weekOffset);

    @Query(value = "select * from t_am_system_config h " +
            "where 1 = 1 " +
            "and if( :#{#config.configCode} is not null, h.config_code like CONCAT('%', :#{#config.configCode}, '%'), 1=1 ) " +
            "and if( :#{#config.configName} is not null, h.config_name like CONCAT('%', :#{#config.configName}, '%'), 1=1 ) " +
            "and if( :#{#config.configValue} is not null, h.config_value like CONCAT('%', :#{#config.configValue}, '%'), 1=1 ) " +
            "", nativeQuery = true)
    Page<SystemConfig> page(Pageable pageable, @NotNull @Param("config") SystemConfig param);
}
